package tr.com.netgis.db;

import java.text.MessageFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import tr.com.netgis.SharedPrefs;
import tr.com.netgis.pojo.LocalMessage;
import tr.com.netgis.pojo.Message;
import tr.com.netgis.pojo.NGLocation;
import tr.com.netgis.util.Constants;
import tr.com.netgis.util.Status;
import tr.com.netgis.util.Utility;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * 
 * @author NetGis
 *
 */
public class NGDbHelper extends SQLiteOpenHelper implements IDbHelper {

	private static final String DB_NAME = "NG_DB";
	private static final int DB_VERSION = 7;

	public static final String TABLE_NAME_LOCATIONS = "Locations";
	public static final String TABLE_NAME_MESSAGES = "Messages";
	public static final String TABLE_NAME_LOCAL_MESSAGES = "LocalMessage";
	
	public static final String COL_LAT = "Lat";
	public static final String COL_LON = "Lon";
	public static final String COL_TIME = "Time";
	public static final String COL_STATUS = "Status";
	public static final String COL_SPEED = "Speed";
	public static final String COL_SENT = "SENT";
	public static final String COL_SELECTED = "Selected";
	public static final String COL_READ = "Read";
	public static final String COL_PROVIDER = "Provider";
	public static final String COL_SENDER = "Sender";
	public static final String COL_TYPE = "Type";
	public static final String COL_COMMAND = "Command";
	public static final String COL_MESSAGE_BODY = "MessageBody";
	public static final String COL_COMMAND_PARAMETER = "CommandParameter";

	private static final String STRING_CREATE_LOCATIONS = MessageFormat.format(
			"CREATE TABLE {0} (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
					"{1} TEXT," + // Lat
					"{2} TEXT," + // Lon
					"{3} DATE," + // Time
					"{4} INTEGER," + //Status
					"{5} TEXT," + //Speed
					"{6} INTEGER DEFAULT 0," + //Sent or unsent
					"{7} TEXT);", //Provider network or gps or null string
					TABLE_NAME_LOCATIONS, 
					COL_LAT,
					COL_LON, 
					COL_TIME, 
					COL_STATUS, 
					COL_SPEED,
					COL_SENT,
					COL_PROVIDER);
	
	private static final String STRING_CREATE_MESSAGES = MessageFormat.format(
			"CREATE TABLE {0} (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
					"{1} TEXT," + // Sender
					"{2} TEXT," + // Time
					"{3} TEXT," + //Command
					"{4} TEXT," + //CommandParameter
					"{5} INTEGER DEFAULT 0," + //Selected or not
					"{6} INTEGER DEFAULT 0" +
					");", 
					TABLE_NAME_MESSAGES, 
					COL_SENDER,
					COL_TIME, 
					COL_COMMAND,
					COL_COMMAND_PARAMETER,
					COL_SELECTED,
					COL_READ);

	private static final String STRING_CREATE_LOCAL_MESSAGES = MessageFormat.format(
			"CREATE TABLE {0} (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
					"{1} DATE," + // Time
					"{2} TEXT," + // Type
					"{3} TEXT," + // Message Body
					"{4} INTEGER DEFAULT 0" + //Selected or not
					");", 
					TABLE_NAME_LOCAL_MESSAGES, 
					COL_TIME, 
					COL_TYPE, 
					COL_MESSAGE_BODY,
					COL_SENT);


	public NGDbHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_LOCATIONS);
		db.execSQL(STRING_CREATE_LOCATIONS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_MESSAGES);
		db.execSQL(STRING_CREATE_MESSAGES);		
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_LOCAL_MESSAGES);
		db.execSQL(STRING_CREATE_LOCAL_MESSAGES);	
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onCreate(db);
        Log.i(DB_NAME, "Upgraded");
	}
	
	public static enum Table {
		LOCATIONS,
		MESSAGES,
		LOCAL_MESSAGES;
	}
	
	public static String [] getColumns (Table table) {
		String[] columns = null;
		if (Table.LOCATIONS.equals(table)) {
	        columns = new String[] {"_id", 
	        		COL_LAT,
					COL_LON, 
					COL_TIME, 
					COL_STATUS, 
					COL_SPEED,
					COL_SENT,
					COL_PROVIDER};
		}
		else if (Table.MESSAGES.equals(table)) {
	        columns = new String[] {"_id", 
	        		COL_SENDER,
					COL_TIME, 
					COL_COMMAND,
					COL_COMMAND_PARAMETER,
					COL_SELECTED,
					COL_READ};
		} else if (Table.LOCAL_MESSAGES.equals(table)) {
	        columns = new String[] {"_id", 
	        		COL_TIME, 
					COL_TYPE, 
					COL_MESSAGE_BODY,
					COL_SENT};			
		}
		return columns;	
	}
	
	public void deleteAllLocations () {
		this.getWritableDatabase().delete(TABLE_NAME_LOCATIONS, "1=1", new String[]{});
		Log.i(DB_NAME, "deleted all locations");
	}
	
	public void deleteAllMessages () {
		this.getWritableDatabase().delete(TABLE_NAME_MESSAGES, "1=1", new String[]{});
		Log.i(DB_NAME, "deleted all messages");
	}

	/**
	 * If main activity destroyed and restarted locations are read from database.
	 * SENT = 3
	 * 
	 * @param location
	 * @param forLog true, location inserted to db for logging.
	 */
	public void insertForLog (NGLocation location) {
		if (existsLocation(location, NGLocation.FORLOG)) { //location sent must be set before
			Log.i(DB_NAME, "Exists -> " + location);
			return;
		}
		
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			db.beginTransaction();
			
			long size = howManyLocations();
			long diff = size - 100;
			Log.i(DB_NAME, Long.toString(diff));
			if (diff >= 0) {				
				Cursor cursor = db.query(true, TABLE_NAME_LOCATIONS, new String[]{"_id"}, COL_SENT + " = " + NGLocation.FORLOG, null, null, null, COL_TIME + " asc", Long.toString(diff));
				for (int i = 0; i < cursor.getCount(); i++) {
					cursor.moveToNext();
					deleteLocation(cursor.getInt(0));
				}
				cursor.close();
			}				
			
			location.setSent(NGLocation.FORLOG);
			ContentValues cv = createCV(location);
			
			db.insert(TABLE_NAME_LOCATIONS, null, cv);
			db.setTransactionSuccessful();
			Log.i(DB_NAME, "inserted for log\n" + location);			  
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
		}	
	}

	public void insertUnsentLocation (NGLocation location) {
		if (existsLocation(location, NGLocation.UNSENT)) {
			Log.i(DB_NAME, "Exists -> " + location);
			return;
		}
		
		SQLiteDatabase db = this.getWritableDatabase();
		try{
			db.beginTransaction();
			
			long size = howManyLocations();
			long diff = size - SharedPrefs.getInstance().getLocationSize();
			Log.i(DB_NAME, Long.toString(diff));
			if (diff >= 0) {				
				Cursor cursor = db.query(true, TABLE_NAME_LOCATIONS, new String[]{"_id"}, COL_SENT + " = " + NGLocation.UNSENT, null, null, null, COL_TIME + " asc", Long.toString(diff));
				for (int i = 0; i < cursor.getCount(); i++) {
					cursor.moveToNext();
					deleteLocation(cursor.getInt(0));
				}
				cursor.close();
			}				
			
			location.setSent(NGLocation.UNSENT);
			ContentValues cv = createCV(location);
			
			db.insert(TABLE_NAME_LOCATIONS, null, cv);
			db.setTransactionSuccessful();
			Log.i(DB_NAME, "inserted\n" + location);			  
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
		}	
	}
	
	private ContentValues createCV (NGLocation location) {
		ContentValues cv = new ContentValues();
		cv.put(COL_LAT, Double.toString(location.getLat()));
		cv.put(COL_LON, Double.toString(location.getLon()));
		cv.put(COL_TIME, Utility.createSimpleDateFormat().format(new Date(location.getTarihSaat())));
		cv.put(COL_STATUS, location.getStatus().getCode());
		cv.put(COL_SPEED, Float.toString(location.getSpeed()));			
		cv.put(COL_SENT, location.getSent());
		cv.put(COL_PROVIDER, location.getProvider());
		return cv;
	}

	/**
	 * {@link NGLocation}
	 * @param sent
	 */
	public void deleteLocations (byte sent) {
		this.getWritableDatabase().delete(TABLE_NAME_LOCATIONS, COL_SENT + " = " + sent , new String[]{});		
		Log.i(DB_NAME, "location deleted by sent = " + sent);
	}
	
	public void deleteLocation (int id){
		this.getWritableDatabase().delete(TABLE_NAME_LOCATIONS, "_id = ?", new String[]{Integer.toString(id)});
		Log.i(DB_NAME, "location deleted " + id);
	}
	
	public void sendLocation (int id){
		deleteLocation(id);
	}
	
	public void sendLocation (int [] ids){
		SQLiteDatabase db = this.getWritableDatabase();
		try{
			db.beginTransaction();
			for (int id : ids) {
				deleteLocation(id);
			}		  
			db.setTransactionSuccessful();
		} catch (SQLException e) {
		} finally {
			db.endTransaction();
		}				
	}
	
	public List<NGLocation> listUnsentLocations (){
		return listLocations("SENT = " + NGLocation.UNSENT);
	}
	
	public List<NGLocation> listLocations (String whereSql){
		List<NGLocation> result = new ArrayList<NGLocation>();
		Cursor cursor = this.getWritableDatabase().query(TABLE_NAME_LOCATIONS, 
				getColumns(Table.LOCATIONS), whereSql, null, null, null, " " + COL_TIME + " DESC " , null);
		for (int i = 0; i < cursor.getCount(); i++) {
			cursor.moveToNext();
			NGLocation location = getLocation(cursor);
			result.add(location);
		}
		cursor.close();
		return result;
	}

	private NGLocation getLocation (Cursor cursor) {
		NGLocation location = new NGLocation();
		location.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
		location.setLat(Double.parseDouble(cursor.getString(cursor.getColumnIndex(COL_LAT))));
		location.setLon(Double.parseDouble(cursor.getString(cursor.getColumnIndex(COL_LON))));
		try {
			location.setTarihSaat(Utility.createSimpleDateFormat().parse(cursor.getString(cursor.getColumnIndex(COL_TIME))).getTime());
		} catch (ParseException e) {
			e.printStackTrace();
		}
		int iStatus = cursor.getInt(cursor.getColumnIndex(COL_STATUS));
		Status status = Status.getByCode(iStatus);
		location.setStatus(status);
		location.setSpeed(Float.parseFloat(cursor.getString(cursor.getColumnIndex(COL_SPEED))));
		location.setSent(new Integer(cursor.getInt(cursor.getColumnIndex(COL_SENT))).byteValue());
		location.setProvider(cursor.getString(cursor.getColumnIndex(COL_PROVIDER)));
		return location;
	}
	
	public List<NGLocation> listLoggedLocations (){
		return listLocations("SENT = " + NGLocation.FORLOG);
	}
	
	/**
	 * 
	 * @return how many records inserted into TABLE_NAME
	 */
	public long howManyLocations (){
		return DatabaseUtils.queryNumEntries(this.getWritableDatabase(), TABLE_NAME_LOCATIONS);
	}

	public long howManyMessages (){
		return DatabaseUtils.queryNumEntries(this.getWritableDatabase(), TABLE_NAME_MESSAGES);
	}
	
	public boolean existsLocation (NGLocation location, byte sent){
		String time = Utility.createSimpleDateFormat().format(new Date(location.getTarihSaat()));
		Cursor cursor = this.getWritableDatabase().query(TABLE_NAME_LOCATIONS, 
				new String[]{"_id"}, COL_TIME  + " = ? AND " + COL_SENT + " = " + sent, new String[]{time}, null, null, null, null);
		int count = cursor.getCount();
		cursor.close();
		return count > 0;
	}

	public void insertMessage(Message message) {
		if (message == null)
			return;
		List<Message> messages = new ArrayList<Message>();
		messages.add(message);
		insertMessages(messages);
	}


	@Override
	public void insertMessages(List<Message> messages) {
		
		long size = howManyLocations();
		SQLiteDatabase db = this.getWritableDatabase();
		try{
			db.beginTransaction();
			long diff = size + messages.size() - Constants.MESSAGES_SIZE;
			Log.i(DB_NAME, "Messages diff: " + Long.toString(diff));
			if (diff >= 0) {
				Cursor cursor = db.query(true, TABLE_NAME_MESSAGES, new String[]{"_id"}, null, null, null, null, COL_TIME + " asc", Long.toString(diff));
				for (int i = 0; i < cursor.getCount(); i++) {
					cursor.moveToNext();
					deleteMessage(cursor.getInt(0));
				}
				cursor.close();
			}
			for (Message message : messages) {
				ContentValues cv = new ContentValues();
				cv.put(COL_SENDER, message.getSender());
				cv.put(COL_TIME, message.getTime());
				cv.put(COL_COMMAND, message.getCommand());
				cv.put(COL_COMMAND_PARAMETER, message.getCommandParameter());
				cv.put(COL_READ, message.getRead());				
				db.insert(TABLE_NAME_MESSAGES, null, cv);
				Log.i(DB_NAME, "inserted\n" + message);			  
			}
			db.setTransactionSuccessful();
		} catch (SQLException e) {
		} finally {
			db.endTransaction();
			db.close();
		}			
	}

	public static Message getMessage (Cursor cursor) {
		Message message = new Message();
		message.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
		message.setSender(cursor.getString(cursor.getColumnIndex(COL_SENDER)));
		message.setTime(cursor.getString(cursor.getColumnIndex(COL_TIME)));
		message.setCommand(cursor.getString(cursor.getColumnIndex(COL_COMMAND)));
		message.setCommandParameter(cursor.getString(cursor.getColumnIndex(COL_COMMAND_PARAMETER)));
		message.setRead(cursor.getInt(cursor.getColumnIndex(COL_READ)));
		return message;
	}
	
	@Override
	public List<Message> listMessages() {
		List<Message> result = new ArrayList<Message>();
		Cursor cursor = this.getWritableDatabase().query(TABLE_NAME_MESSAGES, 
				getColumns(Table.MESSAGES), "1 = 1", null, null, null, null, null);
		for (int i = 0; i < cursor.getCount(); i++) {
			cursor.moveToNext();
			Message message = getMessage(cursor);
			result.add(message);
		}
		cursor.close();
		return result;		
	}

	@Override
	public void deleteMessage(int[] ids) {
		if (ids == null || ids.length == 0) {
			return;
		}
		SQLiteDatabase db = this.getWritableDatabase();
		try{
			db.beginTransaction();
			for (int id : ids) {
				deleteMessage(id);
			}		  
			db.setTransactionSuccessful();
		} catch (SQLException e) {
		} finally {
			db.endTransaction();
		}			
	}


	@Override
	public void deleteMessage(int id) {
		this.getWritableDatabase().delete(TABLE_NAME_MESSAGES, "_id = ?", new String[]{Integer.toString(id)});
		Log.i(DB_NAME, "message deleted " + id);		
	}
	
	public void clearSelections(Table table) {
        ContentValues values = new ContentValues();
        values.put(" Selected", 0);
        if (Table.MESSAGES.equals(table))
        	this.getWritableDatabase().update(TABLE_NAME_MESSAGES, values, null, null);
        else if (Table.LOCATIONS.equals(table)) {
        	this.getWritableDatabase().update(TABLE_NAME_LOCATIONS, values, null, null);        	
        }
        	
    }


	@Override
	public void insertLocalMessage(LocalMessage message) {
		
		SQLiteDatabase db = this.getWritableDatabase();
		try{
			db.beginTransaction();
			long size = howManyLocations();
			long diff = size - Constants.LAST_N_LOCATIONS_SIZE;
			Log.i(DB_NAME, Long.toString(diff));
			if (diff >= 0) {
				
				Cursor cursor = db.query(true, TABLE_NAME_LOCAL_MESSAGES, new String[]{"_id"}, null, null, null, null, COL_TIME + " asc", Long.toString(diff));
				for (int i = 0; i < cursor.getCount(); i++) {
					cursor.moveToNext();
					deleteLocation(cursor.getInt(0));
				}
				cursor.close();
			}
			
			ContentValues cv = new ContentValues();
			cv.put(COL_TIME, Utility.createSimpleDateFormat().format(message.getTime()));
			cv.put(COL_MESSAGE_BODY, message.getMessageBody());
			cv.put(COL_TYPE, message.getType());
			db.insert(TABLE_NAME_LOCAL_MESSAGES, null, cv);
			db.setTransactionSuccessful();
			Log.i(DB_NAME, "inserted\n" + message);			  
		} catch (SQLException e) {
		} finally {
			db.endTransaction();
		}		
		
	}


	@Override
	public void deleteAllLocalMessages() {
		this.getWritableDatabase().delete(TABLE_NAME_LOCAL_MESSAGES, "1=1", new String[]{});
		Log.i(DB_NAME, "deleted all local messages");
		
	}


	@Override
	public void deleteLocalMessage(int[] ids) {
		SQLiteDatabase db = this.getWritableDatabase();
		try{
			db.beginTransaction();
			for (int id : ids) {
				deleteLocalMessage(id);
			}		  
			db.setTransactionSuccessful();
		} catch (SQLException e) {
		} finally {
			db.endTransaction();
		}					
	}


	@Override
	public void deleteLocalMessage(int id) {
		this.getWritableDatabase().delete(TABLE_NAME_LOCAL_MESSAGES, "_id = ?", new String[]{Integer.toString(id)});
		Log.i(DB_NAME, "local message deleted " + id);			
	}

	@Override
	public long howManyLocalMessages() {
		return DatabaseUtils.queryNumEntries(this.getWritableDatabase(), TABLE_NAME_LOCAL_MESSAGES);
	}

	@Override
	public NGLocation getLocationBeforeByMinute(NGLocation location, int minute) {
		String whereSql = "";
		//TODO sql ile kısıtlanabilir, şimdilik bütün konumları çekip içinden seçiyorum
		/*
		whereSql = MessageFormat.format(" (strftime('%s',{0}) - strftime('%s', {1})) / 60 > {2} ",
				location.getDate(),
				COL_TIME,
				minute * 60);
		*/
		List<NGLocation> list = listLocations(whereSql); //time'a göre desc şeklinde geldi.
		for (NGLocation ngLocation : list) {
			if ((location.getTarihSaat() - ngLocation.getTarihSaat()) / (1000 * 60) >= minute )
				return ngLocation;
		}
		return null;
	}
	
}
